So now that we understand the need for data validation.
Let's take a look at incorporating creating data validation.
So I'm still using the same example file.
And I'm on the worksheet called sort and filter.
Now the validation that I want to show off here that I'm going to demonstrate I'm going to work with
the make column.
So as people are editing records adding new records into this list I want them to only be able to pick
from a predefined set of makes and that's it.
Nothing else.
Well before you can apply validation to a list you got to select the cells that you want to apply the
validation to.
So for me I'm going to jump in here and I'm going to grab from A-4 and I'm just going to grab a few
rows down below I'm going to hold down my shift key and I'll grab down to be 30 so that's the group
of cells from before down to be 30 that I want to apply the validation to.
If this was real world and I anticipated that this list was going to grow several hundred or thousands
of records you'd want to grab all of those rows that you're going to apply the validation to anticipate
how big your list is going to get.
So now that I've got the rows selected are the cell selected.
I'm now going to create my validation so I'll go up to my data 10 top my screen inside the data tool
section.
I'm going to go to data validation and I'm going to create data validation.
This opens up my data validation window.
Remember in the background I've got a group of self-selected.
So my settings or validation criteria for that group of cells I'm going to allow not any value does
that then that's that's that's that's open and open at the doorway for people to input incorrect data
.
So I'm not going to allow any value but I'm going to allow one of these validation criteria techniques
.
And for me I'm going to use a lists I'm going to tell them they can only pick from a predefined set
of mates a list that I'm about to create here.
So for my lists I'm going to bring up a source I may say you can pick from Chevvy and he separated by
commas.
So I mean is a Chevy comma Ford comma Pontiac.
Let's make sure I spell that right.
Part D ack comma and what else we got there we got dodge.
All right.
I'm going to stop right there.
I mean to leave off Oldsmobile for a moment because I want to show you something here.
But in reality Oldsmobile may be a correct value but I want to show you what happens if it's not a part
of the list.
So Chevy Ford Pontiac dodge all separated by commas.
That's your validation criteria.
That's your list.
That's what people can pick from now.
So.
OK.
And now let's imagine I want to put in a new record here so I can say it is number 100.
I tab over.
I've now got a job down here where I can pick from the valid makes.
So I've got Ford tab over here is a mustang and so on.
Now see I get down here and here's number a record number 110.
And I jump over here and I say we're going to have a Porsche.
Right.
There's Porsche.
Well Porsche wasn't one of the four mates that you could choose from.
I had my tab key and now I get an error.
Hey the value inchers not valid.
A User's restricted devalues.
OK so I can retry and cancel if I retry.
I can try taking something else in there go to the dropdown menu if I cancel.
Just drop what I did.
So I have to pick from the valide section.
There's a Chevy.
Now remember earlier Oldsmobile that was in my original list and it's not barking at me it's not saying
her.
That's an incorrect value right.
Well that's because it was already in the list.
So it's not validating that one at this point.
But take a look at this.
I'm going to go back to my data tab.
I'll go back to the data validation and I'm going to say circle in invalid data.
And now it's telling me you Oldsmobile and Chrysler are two invalid values that you have in your existing
data.
So not only can you validate new data that's come into the system but you can also jump in here and
find any errors within existing data.
So try this out.
It's really simple data validation.
Highlight the group of cells that you want to validate whether they're old records or new records or
combination of new and old go up to your data time data validation and create a list validation do the
makes up this example right here.
I've just given you or trap the model try out the auto smoking convertible and insurance whatever you
want make up something of your own but try adding data validation.
You're going to take yourself in the end because you're going to get consistent clean data in your system
so that when you go to report on it later you get that same consistent clean data out of your system
as well.
So try it out create some data validation using a list